#include "memberdao.h"
#include <QDebug>

MemberDAO::MemberDAO(Database *db) : m_db(db) {}

QMap<QString, QVariant> MemberDAO::getMemberByCard(const QString &cardNumber)
{
    QString query = "SELECT c.customer_id, c.name, c.phone, c.level_id, ml.level_name, "
                   "c.discount_rate, ca.balance, c.total_recharged as total_spent, "
                   "0 as points " // 假设积分字段，实际可能需要从其他表获取
                   "FROM customer c "
                   "LEFT JOIN membership_level ml ON c.level_id = ml.level_id "
                   "LEFT JOIN customer_account ca ON c.customer_id = ca.customer_id "
                   "WHERE c.phone = ? OR c.customer_id = ?"; // 这里假设手机号作为卡号

    QVector<QMap<QString, QVariant>> result = m_db->query(query, {cardNumber, cardNumber});

    if (result.isEmpty()) {
        return QMap<QString, QVariant>();
    }

    return result.first();
}

QMap<QString, QVariant> MemberDAO::getMemberById(int memberId)
{
    QString query = "SELECT c.customer_id, c.name, c.phone, c.level_id, ml.level_name, "
                   "c.discount_rate, ca.balance, c.total_recharged as total_spent, "
                   "0 as points " // 假设积分字段
                   "FROM customer c "
                   "LEFT JOIN membership_level ml ON c.level_id = ml.level_id "
                   "LEFT JOIN customer_account ca ON c.customer_id = ca.customer_id "
                   "WHERE c.customer_id = ?";

    QVector<QMap<QString, QVariant>> result = m_db->query(query, {memberId});

    if (result.isEmpty()) {
        return QMap<QString, QVariant>();
    }

    return result.first();
}

/**
 * 会员积分更新算法
 * 业务逻辑：更新会员的积分余额，支持积分增加和扣除
 * 更新规则：
 * 1. 正数表示积分增加（消费获得积分）
 * 2. 负数表示积分扣除（积分兑换商品）
 * 3. 需要确保积分不会变为负数
 * 注意：此函数需要根据实际数据库表结构完善实现
 * 
 * @param memberId 会员ID
 * @param points 积分变化量（正数增加，负数扣除）
 * @return 更新是否成功
 */
bool MemberDAO::updateMemberPoints(int memberId, int points)
{
    // 实现积分更新逻辑
    // 这里需要根据实际数据库结构实现
    return true;
}

/**
 * 会员充值处理算法
 * 业务逻辑：执行完整的会员充值流程，包括余额更新、累计充值更新和等级升级
 * 处理流程：
 * 1. 开启数据库事务确保数据一致性
 * 2. 更新会员账户余额
 * 3. 更新会员累计充值金额
 * 4. 检查并处理等级升级
 * 5. 记录等级变更历史（如有升级）
 * 6. 提交事务或回滚
 * 
 * @param memberId 会员ID
 * @param amount 充值金额
 * @param remark 充值备注
 * @return 充值是否成功
 */
bool MemberDAO::processMemberRecharge(int memberId, double amount, const QString &remark)
{
    // 开启事务
    if (!m_db->beginTransaction()) {
        qDebug() << "Failed to begin transaction for member recharge";
        return false;
    }
    
    try {
        // 获取充值前的会员信息
        QMap<QString, QVariant> memberInfo = getCompleteeMemberInfo(memberId);
        if (memberInfo.isEmpty()) {
            m_db->rollbackTransaction();
            qDebug() << "Member not found:" << memberId;
            return false;
        }
        
        int oldLevelId = memberInfo["level_id"].toInt();
        QString oldLevelName = memberInfo["level_name"].toString();
        double oldTotalRecharged = memberInfo["total_recharged"].toDouble();
        QString memberName = memberInfo["name"].toString();
        
        // 1. 更新账户余额
        if (!updateMemberBalance(memberId, amount)) {
            m_db->rollbackTransaction();
            qDebug() << "Failed to update member balance";
            return false;
        }
        
        // 2. 更新累计充值金额
        if (!updateTotalRecharged(memberId, amount)) {
            m_db->rollbackTransaction();
            qDebug() << "Failed to update total recharged";
            return false;
        }
        
        // 3. 计算新的等级
        double newTotalRecharged = oldTotalRecharged + amount;
        int newLevelId = calculateMemberLevel(newTotalRecharged);
        
        // 4. 如果等级有变化，更新等级并记录历史
        if (newLevelId != oldLevelId) {
            if (!updateMemberLevel(memberId, newLevelId)) {
                m_db->rollbackTransaction();
                qDebug() << "Failed to update member level";
                return false;
            }
            
            // 获取新等级名称
            QMap<QString, QVariant> newLevelInfo = getMemberLevelInfo(newLevelId);
            QString newLevelName = newLevelInfo["level_name"].toString();
            
            // 记录等级变更历史
            QString note = QString("充值%1元，累计充值达到%2元，升级为%3")
                          .arg(amount, 0, 'f', 2)
                          .arg(newTotalRecharged, 0, 'f', 2)
                          .arg(newLevelName);
            
            if (!recordLevelChange(memberId, memberName, oldLevelId, oldLevelName,
                                 newLevelId, newLevelName, "1", 0, note)) {
                m_db->rollbackTransaction();
                qDebug() << "Failed to record level change";
                return false;
            }
        }
        
        // 提交事务
        if (!m_db->commitTransaction()) {
            qDebug() << "Failed to commit recharge transaction";
            return false;
        }
        
        qDebug() << "Member recharge successful:" << memberId << "Amount:" << amount;
        return true;
        
    } catch (...) {
        m_db->rollbackTransaction();
        qDebug() << "Exception occurred during member recharge";
        return false;
    }
}

bool MemberDAO::updateTotalRecharged(int memberId, double amount)
{
    QString query = "UPDATE customer SET total_recharged = total_recharged + ? WHERE customer_id = ?";
    return m_db->execute(query, {amount, memberId});
}

bool MemberDAO::updateMemberLevel(int memberId, int newLevelId)
{
    // 获取新等级的折扣率
    QMap<QString, QVariant> levelInfo = getMemberLevelInfo(newLevelId);
    if (levelInfo.isEmpty()) {
        qDebug() << "Invalid level ID:" << newLevelId;
        return false;
    }
    
    double discountRate = levelInfo["discount_rate"].toDouble();
    
    QString query = "UPDATE customer SET level_id = ?, discount_rate = ? WHERE customer_id = ?";
    return m_db->execute(query, {newLevelId, discountRate, memberId});
}

bool MemberDAO::recordLevelChange(int memberId, const QString &memberName, int oldLevelId,
                                const QString &oldLevelName, int newLevelId,
                                const QString &newLevelName, const QString &changeReason,
                                int operatorId, const QString &note)
{
    QString query = "INSERT INTO membership_level_history "
                   "(customer_id, customer_name, old_level_id, old_level_name, "
                   "new_level_id, new_level_name, change_reason, operator_id, note) "
                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
    
    QVector<QVariant> params = {memberId, memberName, oldLevelId, oldLevelName,
                               newLevelId, newLevelName, changeReason,
                               operatorId > 0 ? QVariant(operatorId) : QVariant(),
                               note};
    
    return m_db->execute(query, params);
}

QMap<QString, QVariant> MemberDAO::getMemberLevelInfo(int levelId)
{
    QString query = "SELECT level_id, level_name, threshold, discount_rate "
                   "FROM membership_level WHERE level_id = ?";
    
    QVector<QMap<QString, QVariant>> result = m_db->query(query, {levelId});
    
    if (result.isEmpty()) {
        return QMap<QString, QVariant>();
    }
    
    return result.first();
}

int MemberDAO::calculateMemberLevel(double totalRecharged)
{
    // 根据数据库中的等级门槛计算等级
    QString query = "SELECT level_id FROM membership_level "
                   "WHERE threshold <= ? ORDER BY threshold DESC LIMIT 1";
    
    QVector<QMap<QString, QVariant>> result = m_db->query(query, {totalRecharged});
    
    if (result.isEmpty()) {
        return 0; // 非会员
    }
    
    return result.first()["level_id"].toInt();
}

bool MemberDAO::updateMemberBalance(int memberId, double amount)
{
    QString query = "UPDATE customer_account SET balance = balance + ? WHERE customer_id = ?";
    return m_db->execute(query, {amount, memberId});
}

/**
 * 会员余额扣除算法
 * 业务逻辑：安全地从会员账户中扣除指定金额
 * 扣除流程：
 * 1. 查询会员当前余额
 * 2. 验证余额是否足够支付
 * 3. 执行余额扣除操作
 * 安全机制：先检查后扣除，防止余额不足或负数余额
 * 
 * @param memberId 会员ID
 * @param amount 扣除金额
 * @return 扣除是否成功
 */
bool MemberDAO::deductBalance(int memberId, double amount)
{
    // 检查余额是否足够
    QString checkQuery = "SELECT balance FROM customer_account WHERE customer_id = ?";
    QVector<QMap<QString, QVariant>> result = m_db->query(checkQuery, {memberId});

    if (result.isEmpty() || result.first()["balance"].toDouble() < amount) {
        return false;
    }

    // 扣除余额
    QString updateQuery = "UPDATE customer_account SET balance = balance - ? WHERE customer_id = ?";
    return m_db->execute(updateQuery, {amount, memberId});
}

bool MemberDAO::addMemberConsumption(int memberId, double amount)
{
    QString query = "UPDATE customer SET total_recharged = total_recharged + ? WHERE customer_id = ?";
    return m_db->execute(query, {amount, memberId});
}

QMap<QString, QVariant> MemberDAO::getCompleteeMemberInfo(int memberId)
{
    QString query = "SELECT c.customer_id, c.name, c.phone, c.level_id, ml.level_name, "
                   "c.discount_rate, c.register_time, c.total_recharged, "
                   "ca.balance, ca.create_time as account_create_time, ca.last_update "
                   "FROM customer c "
                   "LEFT JOIN membership_level ml ON c.level_id = ml.level_id "
                   "LEFT JOIN customer_account ca ON c.customer_id = ca.customer_id "
                   "WHERE c.customer_id = ?";

    QVector<QMap<QString, QVariant>> result = m_db->query(query, {memberId});

    if (result.isEmpty()) {
        return QMap<QString, QVariant>();
    }

    return result.first();
}

QVector<QMap<QString, QVariant>> MemberDAO::getMemberLevelHistory(int memberId)
{
    QString query = "SELECT history_id, customer_name, old_level_id, old_level_name, "
                   "new_level_id, new_level_name, change_reason, change_time, note "
                   "FROM membership_level_history "
                   "WHERE customer_id = ? "
                   "ORDER BY change_time DESC";

    return m_db->query(query, {memberId});
}

QVector<QMap<QString, QVariant>> MemberDAO::getAllMembers()
{
    QString query = "SELECT c.customer_id, c.name, c.phone, c.level_id, ml.level_name, "
                   "c.discount_rate, c.register_time, c.total_recharged, ca.balance "
                   "FROM customer c "
                   "LEFT JOIN membership_level ml ON c.level_id = ml.level_id "
                   "LEFT JOIN customer_account ca ON c.customer_id = ca.customer_id "
                   "ORDER BY c.customer_id";

    qDebug() << "DAO层执行SQL查询:" << query;
    QVector<QMap<QString, QVariant>> result = m_db->query(query);
    qDebug() << "DAO层查询结果数量:" << result.size();
    if (!result.isEmpty()) {
        qDebug() << "DAO层第一条记录:" << result.first();
    }
    return result;
}

/**
 * 添加新会员
 * 业务逻辑：向数据库插入新会员记录，并创建对应的账户记录
 * 插入流程：
 * 1. 插入会员基本信息到customer表
 * 2. 为新会员创建账户记录到customer_account表
 * 3. 使用事务确保数据一致性
 * 
 * @param name 会员姓名
 * @param phone 会员手机号
 * @return 添加是否成功
 */
bool MemberDAO::addMember(const QString &name, const QString &phone)
{
    // 开始事务
    if (!m_db->beginTransaction()) {
        qDebug() << "Failed to begin transaction";
        return false;
    }

    try {
        // 1. 插入会员基本信息
        QString insertMemberQuery = "INSERT INTO customer (name, phone, level_id, discount_rate, total_recharged) "
                                   "VALUES (?, ?, 0, 1.00, 0.00)";
        
        if (!m_db->execute(insertMemberQuery, {name, phone})) {
            m_db->rollbackTransaction();
            qDebug() << "Failed to insert member";
            return false;
        }

        // 2. 获取新插入的会员ID
        QString getIdQuery = "SELECT LAST_INSERT_ID() as customer_id";
        QVector<QMap<QString, QVariant>> result = m_db->query(getIdQuery);
        
        if (result.isEmpty()) {
            m_db->rollbackTransaction();
            qDebug() << "Failed to get new member ID";
            return false;
        }

        int customerId = result.first()["customer_id"].toInt();

        // 3. 为新会员创建账户记录
        QString insertAccountQuery = "INSERT INTO customer_account (customer_id, balance) VALUES (?, 0.00)";
        
        if (!m_db->execute(insertAccountQuery, {customerId})) {
            m_db->rollbackTransaction();
            qDebug() << "Failed to create member account";
            return false;
        }

        // 提交事务
        if (!m_db->commitTransaction()) {
            qDebug() << "Failed to commit transaction";
            return false;
        }

        qDebug() << "Successfully added new member:" << name << "with phone:" << phone;
        return true;

    } catch (...) {
        m_db->rollbackTransaction();
        qDebug() << "Exception occurred while adding member";
        return false;
    }
}
